package dao.aplicacao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import modelos.aplicacao.FinalizaModel;

import dao.BaseDao;

public class FinalizaDao extends BaseDao {

	public int getIdModelo(int idVersao) throws SQLException{
		
		String sql = "SELECT `id_modelo` FROM `versao` WHERE `id` = " + idVersao;
		
		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result =  ps.executeQuery();
		
		while(result.next())
			return result.getInt("id_modelo");
		
		return 0;
	}
	
	public ArrayList<FinalizaModel> getAcessorios(int idKit) throws SQLException{
		
		ArrayList<FinalizaModel> acessorios = new ArrayList<FinalizaModel>();
		
		String sql = 	"SELECT * " +
						"FROM `acessorios` " +
						"WHERE `id` NOT IN (" + this.getStringAcessoriosKit(idKit) + ");";
		
		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result =  ps.executeQuery();
		
		while(result.next()){
			
			FinalizaModel acessorio = new FinalizaModel();
			
			acessorio.setIdAcessorio(result.getInt("id"));
			acessorio.setNomeAcessorio(result.getString("nome"));
			acessorio.setPreco(result.getDouble("preco"));
			
			acessorios.add(acessorio);
		}
		
		return acessorios;
	}
	
	public String getStringAcessoriosKit(int idKit) throws SQLException{
		
		String sql = 	"SELECT CONCAT(`id_acessorios_1`,',' ,`id_acessorios_2`,',',`id_acessorios_3`,',',`id_acessorios_4`,',',`id_acessorios_5`) as acessorios " +
						"FROM `kit` " +
						"WHERE `id` = "+ idKit +" ";
		
		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result =  ps.executeQuery();
		
		while(result.next()){
			return result.getString("acessorios");
		}
		
		return "";
	}
	
	public String getNomeVersao(int idVersao) throws SQLException{
		
		String sql = 	"SELECT `nome` " +
				"FROM `versao` " +
				"WHERE `id` = "+ idVersao +" ";

		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result =  ps.executeQuery();
		
		while(result.next())
			return result.getString("nome");
		
		return "";
	}
	
	public String getNomeKit(int idKit) throws SQLException{
		
		String sql = 	"SELECT `nome` " +
				"FROM `kit` " +
				"WHERE `id` = "+ idKit +" ";

		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result =  ps.executeQuery();
		
		while(result.next())
			return result.getString("nome");
		
		return "";
	}
	
	public String getNomeCor(int idCor) throws SQLException{
		
		String sql = 	"SELECT `nome` " +
				"FROM `cores` " +
				"WHERE `id` = "+ idCor +" ";

		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result =  ps.executeQuery();
		
		while(result.next())
			return result.getString("nome");
		
		return "";
	}
	
	public ArrayList<FinalizaModel> getConcessionarias() throws SQLException{
		
		ArrayList<FinalizaModel> concessionarias = new ArrayList<FinalizaModel>();
		
		String sql = 	"SELECT * " +
						"FROM `concessionarias` ";
		
		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result =  ps.executeQuery();
		
		while(result.next()){
			
			FinalizaModel conce = new FinalizaModel();
			
			conce.setRazao_social(result.getString("razao_social"));
			
			concessionarias.add(conce);
		}
		
		return concessionarias;
		
	}
	
	public void cadastraPedido(String nome_completo, String email, String cpf, int idVersao, int idCor, int idKit, String id_concessionarias1, String id_concessionarias2, String id_concessionarias3) throws SQLException{
		
		String sql = 	"INSERT INTO `pedidos` " +
						"(`id`, `data`, `nome_completo`, `cpf`, `email`, " +
						"`id_marca`, `id_modelo`, `id_versao`, `id_cores`, `id_kits`, " +
						"`acessorios`, `valor_total`, " +
						"`id_concessionarias1`, `id_concessionarias2`, `id_concessionarias3`, " +
						"`chave_pedido`, `estatus`) " +
						"VALUES " +
						"(NULL, NOW() , '" + nome_completo +  "', '" + cpf + "', '" + email + "', " +
						" " + this.getIdMarca(idVersao) +", " + this.getIdModelo(idVersao) + ", " + idVersao + ", " + idCor + ", " + idKit + ", " +
						"NULL, " + this.sumValorTotal(idVersao, idKit) + ", " +
						" " + this.getIdConcessionaria(id_concessionarias1) + ", " + this.getIdConcessionaria(id_concessionarias2) + ", " + this.getIdConcessionaria(id_concessionarias3) + ", " +
						" md5('" + Math.random() + Math.random() + "'), 0)";

		PreparedStatement ps = con.prepareStatement(sql);
		
		ps.executeUpdate(sql);
		
	}
	
	public int getIdMarca( int idVersao) throws SQLException{
		
		String sql = "SELECT `id_marca` FROM `versao` WHERE `id` = "+idVersao;
		
		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result = ps.executeQuery();
		
		while(result.next())
			return result.getInt("id_marca");
		
		return 0;
		
	}
	
	public double sumValorTotal(int idVersao, int idKit) throws SQLException{
		
		String sql = 	"SELECT SUM(total) as valortotal FROM( " +
							"SELECT `valor` as total FROM `versao` WHERE `id` = " + idVersao + " " +
						"UNION ALL " +
							"SELECT `preco` as total FROM `kit` WHERE `id` = " + idKit + " " + 
						")a ";
		
		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result = ps.executeQuery();
		
		while(result.next())
			return result.getDouble("valortotal");
		
		return 0d;
	}
	
	public boolean verificaCpfDia(String cpf) throws SQLException{
		
		String sql = "SELECT COUNT(*) as total FROM pedidos WHERE DATE(`data`) = DATE(NOW()) AND `cpf` = '"+cpf+"' ";
		
		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result = ps.executeQuery();
		
		int total = 0;
		
		while(result.next())
			total = result.getInt("total");
		
		if(total >= 3)
			return true;
		else
			return false;
	}
	
	public int getUltimoPedido() throws SQLException{
		
		String sql = "SELECT id FROM pedidos ORDER BY `id` DESC LIMIT 1";
		
		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result = ps.executeQuery();
		
		while(result.next())
			return result.getInt("id");
		
		return 0;
	}
	
	public int getIdConcessionaria(String conce) throws SQLException{
		
		String sql = "SELECT id FROM concessionarias WHERE `razao_social` = '" + conce + "'";
		
		PreparedStatement ps = con.prepareStatement(sql);
		
		ResultSet result = ps.executeQuery();
		
		while(result.next())
			return result.getInt("id");
		
		return 0;
		
	}
	
}
